package com.learn.core.mapper;

import com.learn.core.domain.LExamUser;
import com.learn.framework.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * Description
 *
 * @author zhaohongyu
 * @ClassName LExamUserMapper
 * @date 2021.04.13 10:28
 */
@Mapper
public interface LExamUserMapper extends BaseMapper<LExamUser> {
    @Select("select sum(case when user_score between 90 and 100 then 1 else 0 end) as '90-100'," +
            "sum(case when user_score between 80 and 89 then 1 else 0 end) as '80-89'," +
            "sum(case when user_score between 70 and 79 then 1 else 0 end) as '70-79'," +
            "sum(case when user_score between 60 and 69 then 1 else 0 end) as '60-69'," +
            "sum(case when user_score between 50 and 59 then 1 else 0 end) as '50-59'," +
            "sum(case when user_score between 40 and 49 then 1 else 0 end) as '40-49'," +
            "sum(case when user_score between 30 and 39 then 1 else 0 end) as '30-39'," +
            "sum(case when user_score between 20 and 29 then 1 else 0 end) as '20-29'," +
            "sum(case when user_score between 10 and 19 then 1 else 0 end) as '10-19'," +
            "sum(case when user_score <10 then 1 else 0 end) as '0-9' from l_exam_user where exam_id = ${exam_id}")
    List<Map<String, Object>> selectNum1(@Param("exam_id") Integer id);

    @Select("select sum(case when user_score between 85 and 100 then 1 else 0 end) as '85-100'," +
            "sum(case when user_score between 70 and 84 then 1 else 0 end) as '70-84'," +
            "sum(case when user_score between 55 and 69 then 1 else 0 end) as '55-69'," +
            "sum(case when user_score between 40 and 54 then 1 else 0 end) as '40-54'," +
            "sum(case when user_score between 25 and 39 then 1 else 0 end) as '25-39'," +
            "sum(case when user_score <20 then 1 else 0 end) as '0-19' from l_exam_user where exam_id = ${exam_id}")
    List<Map<String, Object>> selectNum3(@Param("exam_id") Integer id);

    @Select("select sum(case when user_score between 90 and 100 then 1 else 0 end) as '90-100'," +
            "sum(case when user_score between 75 and 89 then 1 else 0 end) as '75-89'," +
            "sum(case when user_score between 60 and 74 then 1 else 0 end) as '60-74'," +
            "sum(case when user_score between 45 and 59 then 1 else 0 end) as '45-59'," +
            "sum(case when user_score between 30 and 44 then 1 else 0 end) as '30-44'," +
            "sum(case when user_score between 15 and 29 then 1 else 0 end) as '15-29'," +
            "sum(case when user_score <15 then 1 else 0 end) as '0-14' from l_exam_user where exam_id = ${exam_id}")
    List<Map<String, Object>> selectNum2(@Param("exam_id") Integer id);
}
